using System;
using System.Collections.Generic;
using System.Collections;
using System.Text;
using System.Reflection;
using System.Diagnostics;

using phorm.database.core;
using phorm.model.fields;

namespace phorm.database.mssql
{
    /// <summary>
    /// Static class used for create, read, update, and delete MSSQL commands
    /// </summary>
    static class CRUD
    {
        /// <summary>
        /// Run a database creation query.
        /// </summary>
        /// <param name="name">Name of the database</param>
        public static string createDatabase(String name)
        {
            return string.Format("IF db_id('{0}') IS NULL CREATE DATABASE \"{0}\"", name);
        }

        public static string setQuotes()
        {
            return "SET QUOTED_IDENTIFIER ON";
        }

        public static string selectDatabase(String name)
        {
            return string.Format("USE \"{0}\"", name);
        }

        /// <summary>
        /// Build a create table query.
        /// </summary>
        /// <param name="obj">DatabaseRecord to be created</param>
        /// <returns>Create table query</returns>
        public static string buildTable(DatabaseRecord obj)
        {
            Type type = Type.GetType(obj.ToString());
            List<string> foreignKeys = new List<string>();

            StringBuilder sql = new StringBuilder("");

            sql.AppendFormat("IF object_id('{0}', 'U') IS NULL CREATE TABLE \"{0}\" (", type.Name.ToLower());

            Type foreignKeyType = typeof(ForeignKey<>);

            foreach (KeyValuePair<string, DatabaseColumn> column in obj.columns)
            {
                if (column.Value is PrimaryKey)
                {
                    PrimaryKey tmp = column.Value as PrimaryKey;

                    string auto_increment = tmp.auto_increment ? "IDENTITY(1,1)" : "";
                    sql.AppendFormat("\"{0}\" {1}", column.Key, tmp.field_type);
                    sql.AppendFormat((tmp.length == null ? "" : string.Format("({0}) NOT NULL", tmp.length)));
                    sql.AppendFormat(" {0}, ", auto_increment);
                }
                else if (!(column.Value is IIgnorableColumn))
                {
                    DatabaseColumn tmp = column.Value as DatabaseColumn;
                    Type currentType = tmp.GetType();

                    if (currentType.Name.Equals(foreignKeyType.Name))
                    {
                        DatabaseRecord relation = tmp.GetType().GetField("relative").GetValue(tmp) as DatabaseRecord;
                        PrimaryKey primaryKey = relation.GetType().GetField("primary_key").GetValue(relation) as PrimaryKey;
                        Type relationType = relation.GetType();

                        foreignKeys.Add(string.Format(" FOREIGN KEY ({0}) REFERENCES \"{1}\" ({2}) ON DELETE CASCADE, ", tmp.column, relationType.Name.ToString().ToLower(), primaryKey.column));
                    }

                    sql.AppendFormat("\"{0}\" {1}", column.Key, tmp.field_type);
                    sql.Append((tmp.length == null ? "" : string.Format("({0}) NULL", tmp.length)));
                    sql.Append(", ");
                }
            }

            if (obj.primary_key != null)
                sql.AppendFormat("PRIMARY KEY ({0})", obj.primary_key.column);

            if (foreignKeys.Count > 0)
            {
                sql.Append(", ");
                foreignKeys.ForEach(
                    delegate(string value)
                    {
                        sql.Append(value);
                    });
                sql.Remove((sql.Length - 2), 2);
            }

            sql.Append(" )");
            return sql.ToString();
        }

        /// <summary>
        /// Build a save query
        /// </summary>
        /// <param name="obj">DatabaseRecord to be saved</param>
        /// <returns>Insert or Update query based on if object is new</returns>
        public static string buildSaveQuery(DatabaseRecord obj)
        {
            StringBuilder sql = new StringBuilder("");

            if (obj.IsNew)
            {
                sql.AppendFormat("INSERT INTO \"{0}\" ( ", obj.GetType().Name.ToLower());

                foreach (KeyValuePair<string, DatabaseColumn> column in obj.columns)
                {
                    if (!(column.Value is IIgnorableColumn) && (column.Value.hasChanged || column.Value is IInsertOnlyColumn))
                    {
                        if (column.Value is IInsertOnlyColumn)
                            (column.Value as IInsertOnlyColumn).insert();

                        if (column.Value is PrimaryKey)
                        {
                            if ((column.Value as PrimaryKey).auto_increment == false)
                                sql.AppendFormat("\"{0}\", ", column.Key);
                        }
                        else
                            sql.AppendFormat("\"{0}\", ", column.Key);
                    }
                }

                sql.Remove((sql.Length - 2), 2);
                sql.Append(" ) VALUES ( ");

                foreach (KeyValuePair<string, DatabaseColumn> column in obj.columns)
                {
                    if (!(column.Value is IIgnorableColumn) && (column.Value.hasChanged || column.Value is IInsertOnlyColumn))
                    {
                        if (column.Value is IAutoUpdateColumn)
                            (column.Value as IAutoUpdateColumn).update();

                        if (column.Value is PrimaryKey)
                        {
                            if ((column.Value as PrimaryKey).auto_increment == false)
                                sql.AppendFormat("'{0}', ", column.Value.value);
                        }
                        else
                        {
                            sql.AppendFormat("'{0}', ", column.Value.value);
                        }
                        column.Value.hasChanged = false;
                    }
                }

                sql.Remove((sql.Length - 2), 2);
                sql.Append(")");
            }
            else
            {
                sql.AppendFormat("UPDATE \"{0}\" SET ", obj.GetType().Name.ToLower());
                foreach (KeyValuePair<string, DatabaseColumn> column in obj.columns)
                {
                    if (column.Value is IAutoUpdateColumn)
                        (column.Value as IAutoUpdateColumn).update();

                    if (column.Value.hasChanged && !(column.Value is IIgnorableColumn) && !(column.Value is IInsertOnlyColumn) && !(column.Value is PrimaryKey))
                        sql.AppendFormat("\"{0}\"={1}, ",
                            column.Key,
                            column.Value.value == null ? "NULL" : string.Format("'{0}'", column.Value.value)
                            );
                    column.Value.hasChanged = false;
                }

                sql.Remove((sql.Length - 2), 2);
                sql.AppendFormat(" WHERE \"{0}\"='{1}'", obj.primary_key.column, obj.primary_key.value);
            }

            return sql.ToString();
        }

        /// <summary>
        /// Build a select query based on a type of DatabaseRecord
        /// </summary>
        /// <param name="record">DatabaseRecord to be selected</param>
        /// <returns>Select query</returns>
        public static string buildSelectQuery(DatabaseRecord record)
        {
            List<Hashtable> leftJoins = new List<Hashtable>();
            string alias = record.GetType().Name.ToLower();
            StringBuilder query = new StringBuilder("SELECT ");

            foreach (KeyValuePair<string, DatabaseColumn> column in record.columns)
            {
                if (!(column.Value is IIgnorableColumn))
                {
                    if (column.Value is ForeignKey<DatabaseRecord>)
                    {
                        query.Append(string.Format("\"{0}\".\"{1}\" {0}_{1}, ", alias, column.Key));

                        string newAlias = (column.Value as ForeignKey<DatabaseRecord>).relative.GetType().ToString();
                        string pkColumn = (column.Value as ForeignKey<DatabaseRecord>).relative.primary_key.column;

                        Hashtable leftJoin = new Hashtable();
                        leftJoin.Add("table", (column.Value as ForeignKey<DatabaseRecord>).relative.GetType().ToString().ToLower());
                        leftJoin.Add("column", column.Key.ToLower());
                        leftJoin.Add("alias", newAlias);
                        leftJoin.Add("dbColumn", column);
                        leftJoins.Add(leftJoin);

                        foreach (KeyValuePair<string, DatabaseColumn> foreignColumn in (record.columns[column.Key] as ForeignKey<DatabaseRecord>).relative.columns)
                            if (!(foreignColumn.Value is IIgnorableColumn))
                                query.Append(string.Format("\"{0}\".\"{1}\" {0}_{1}, ", newAlias, foreignColumn.Key, newAlias, foreignColumn.Key));
                    }
                    else
                        query.Append(string.Format("\"{0}\".\"{1}\" {0}_{1}, ", alias, column.Key));
                }
            }

            // get rid of the last comma
            query.Remove(query.Length - 2, 2);

            query.Append(string.Format(" FROM \"{0}\" \"{1}\"", record.GetType().Name.ToLower(), alias));

            if (leftJoins.Count > 0)
                foreach (Hashtable join in leftJoins)
                    query.Append(string.Format(" LEFT JOIN \"{0}\" ON \"{1}\".\"{2}\" = \"{3}\".\"{4}\"", join["table"], alias, join["column"], join["alias"], join["dbColumn"]));

            return query.ToString();
        }

        /// <summary>
        /// Build a select query of a specific primary key.
        /// </summary>
        /// <param name="record">DatabaseRecord to be created</param>
        /// <param name="primaryKey">Primary Key of the record to select</param>
        /// <returns>Select query for a primary key selection</returns>
        public static string buildPrimaryKeyQuery(DatabaseRecord record, object primaryKey)
        {
            string alias = record.GetType().Name.ToLower();
            StringBuilder query = new StringBuilder(CRUD.buildSelectQuery(record));
            query.Append(string.Format(" WHERE \"{0}\".\"{1}\" = '{2}'", alias, record.primary_key.column, primaryKey.ToString()));
            return query.ToString();
        }

        /// <summary>
        /// Build a delete query for a database record.
        /// </summary>
        /// <param name="record">The record to be deleted</param>
        /// <returns>Delete query</returns>
        public static string buildDeleteQuery(DatabaseRecord record)
        {
            return string.Format("DELETE FROM \"{0}\" WHERE \"{1}\"='{2}'", record.GetType().Name.ToLower(), record.primary_key.column, record.primary_key.value.ToString());
        }

        /// <summary>
        /// Build a empty query for a database record type.
        /// </summary>
        /// <param name="record">Record type to be emptied</param>
        /// <returns>Empty query</returns>
        public static string buildEmptyQuery(DatabaseRecord record)
        {
            return string.Format("DELETE FROM \"{0}\"", record.GetType().Name.ToLower());
        }

        /// <summary>
        /// Build a drop query for a database record type.
        /// </summary>
        /// <param name="record">Record type to be dropped</param>
        /// <returns>Drop query</returns>
        public static string buildDropQuery(DatabaseRecord record)
        {
            return string.Format("DROP TABLE \"{0}\"", record.GetType().Name.ToLower());
        }

        /// <summary>
        /// Build a pivot table create query for a many-to-many relationship
        /// </summary>
        /// <param name="column">Many-to-many column</param>
        /// <returns>Pivot table create query</returns>
        public static string createManyToMany(BaseManyToMany column)
        {
            Type column2Type = column.GetType().GetGenericArguments()[0];
            Type column1Type = column.model.GetType();

            DatabaseRecord column2Record = Activator.CreateInstance(column2Type) as DatabaseRecord;

            string column1 = string.Format("{0}_{1}", column1Type.Name.ToLower(), column.model.primary_key.column.ToLower());
            string column2 = string.Format("{0}_{1}", column2Type.Name.ToLower(), column2Record.primary_key.column.ToLower());

            if (column1 == column2)
            {
                column1 += DatabaseRecord.SELF;
                column2 += DatabaseRecord.RELATIVE;
            }

            string sql = string.Format("IF object_id('{0}', 'U') IS NULL CREATE TABLE \"{0}\" ( " +
                                       "\"{1}\" {3}{4}NOT NULL, " +
                                       "\"{2}\" {5}{6}NOT NULL, " +
                                       "PRIMARY KEY ({1}, {2}), " +
                                       "FOREIGN KEY ({1}) REFERENCES \"{7}\" ({8}) ON DELETE CASCADE, " +
                                       "FOREIGN KEY ({2}) REFERENCES \"{9}\" ({10}) ON DELETE {11} " +
                                       ") ",
                                        column.pivot_table,
                                        column1,
                                        column2,
                                        column.model.primary_key.field_type,
                                        column.model.primary_key.length == null ? " " : "(" + column.model.primary_key.length + ") ",
                                        column2Record.primary_key.field_type,
                                        column2Record.primary_key.length == null ? " " : "(" + column2Record.primary_key.length + ") ",
                                        column1Type.Name.ToLower(),
                                        column.model.primary_key.column,
                                        column2Type.Name.ToLower(),
                                        column2Record.primary_key.column,
                                        column1.Replace(DatabaseRecord.SELF, "") == column2.Replace(DatabaseRecord.RELATIVE, "") ? "NO ACTION" : "CASCADE"
                                        );
            return sql;
        }

        /// <summary>
        /// Build a select query for a many-to-many column
        /// </summary>
        /// <param name="column">Many-to-many column</param>
        /// <returns>Select query</returns>
        public static string allManyToMany(BaseManyToMany column)
        {
            Type column2Type = column.GetType().GetGenericArguments()[0];
            Type column1Type = column.model.GetType();

            DatabaseRecord column2Record = Activator.CreateInstance(column2Type) as DatabaseRecord;

            string column1 = string.Format("{0}_{1}", column1Type.Name.ToLower(), column.model.primary_key.column.ToLower());
            string column2 = string.Format("{0}_{1}", column2Type.Name.ToLower(), column2Record.primary_key.column.ToLower());

            if (column1 == column2)
            {
                column1 += DatabaseRecord.SELF;
                column2 += DatabaseRecord.RELATIVE;
            }

            StringBuilder query = new StringBuilder("SELECT ");

		    foreach(KeyValuePair<string, DatabaseColumn> col in column2Record.columns)
			    if(!(col.Value is IIgnorableColumn))
				    query.Append(string.Format("\"{0}\".\"{1}\", ", column2Type.Name.ToLower(), col.Key));

            query.Remove(query.Length - 2, 2);

            query.Append(string.Format(
                " FROM \"{0}\", \"{1}\"" +
                " WHERE \"{0}\".\"{2}\" = \"{1}\".\"{3}\"" +
                " AND \"{0}\".\"{4}\" = '{5}'",
                column.pivot_table,
                column2Type.Name.ToLower(),
                column2,
                column2Record.primary_key.column,
                column1,
                column.model.primary_key.value
                ));

            return query.ToString();
        }

        /// <summary>
        /// Build a query to insert a many-to-many relationship
        /// </summary>
        /// <param name="column">Many-to-many column</param>
        /// <param name="relationship">Related record to column</param>
        /// <returns>Insert query</returns>
        public static string addManyToMany(BaseManyToMany column, DatabaseRecord relationship)
        {
            Type column2Type = column.GetType().GetGenericArguments()[0];
            Type column1Type = column.model.GetType();

            DatabaseRecord column2Record = Activator.CreateInstance(column2Type) as DatabaseRecord;

            string column1 = string.Format("{0}_{1}", column1Type.Name.ToLower(), column.model.primary_key.column.ToLower());
            string column2 = string.Format("{0}_{1}", column2Type.Name.ToLower(), column2Record.primary_key.column.ToLower());

            if (column1 == column2)
            {
                column1 += DatabaseRecord.SELF;
                column2 += DatabaseRecord.RELATIVE;
            }

            return string.Format(
                "INSERT INTO \"{0}\" (\"{1}\", \"{2}\") VALUES ('{3}', '{4}')",
                column.pivot_table,
                column1,
                column2,
                column.model.primary_key.value,
                relationship.primary_key.value
                );
        }

        /// <summary>
        /// Build a query to remove a many-to-many relationship
        /// </summary>
        /// <param name="column">Many-to-many column</param>
        /// <param name="relationship">Related record to column</param>
        /// <returns>Delete query</returns>
        public static string removeManyToMany(BaseManyToMany column, DatabaseRecord relationship)
        {
            Type column2Type = column.GetType().GetGenericArguments()[0];
            Type column1Type = column.model.GetType();

            DatabaseRecord column2Record = Activator.CreateInstance(column2Type) as DatabaseRecord;

            string column1 = string.Format("{0}_{1}", column1Type.Name.ToLower(), column.model.primary_key.column.ToLower());
            string column2 = string.Format("{0}_{1}", column2Type.Name.ToLower(), column2Record.primary_key.column.ToLower());

            if (column1 == column2)
            {
                column1 += DatabaseRecord.SELF;
                column2 += DatabaseRecord.RELATIVE;
            }

            return string.Format(
                "DELETE FROM \"{0}\" WHERE \"{1}\"='{3}' AND \"{2}\"='{4}'",
                column.pivot_table,
                column1,
                column2,
                column.model.primary_key.value,
                relationship.primary_key.value
                );
        }
    }
}
